#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.db import connection
from django.db.models import Q

from web.domain.main_contract import MainContract
from web.util.datetime_util import DatetimeUtil
from web.dao.base_dao import BaseDao
from web.models.commodity_future_date_contract_data import CommodityFutureDateContractData
from web.constants.datetime_format import DatetimeFormat

class CommodityFutureDateContractDataDao(BaseDao):
    """
    CommodityFutureDataData的dao类
    """

    model_class = CommodityFutureDateContractData

    ########################################### 全量计算数据 ########################################

    def write_date_basic_data(self):
        """
        计算日线级别全部last_close_price、rising_and_falling_amount和price_change字段
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_BASIC_DATA()")

    def write_date_all_ma(self):
        """
        计算日线级别全部MA数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.CALCULATE_FIVE()")
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.CALCULATE_TEN()")
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.CALCULATE_TWENTY()")
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.CALCULATE_SIXTY()")
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.CALCULATE_ONEHUNDREDTWENTY()")
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.CALCULATE_TWOHUNDREDFIFTY()")

    def write_date_all_macd(self):
        """
        计算日线级别全部MACD数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_MACD_INIT()")
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_MACD_EMA()")
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_MACD_DIF()")
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_MACD_DEA()")
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_MACD()")

    def write_date_all_kd(self):
        """
        计算日线级别全部KD数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_KD_INIT()")
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_KD_RSV()")
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_KD_K()")
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_KD_D()")

    def write_date_all_boll(self):
        """
        计算日线级别全部boll数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.CAL_BOLL()")

    def write_date_all_hei_kin_ashi(self):
        """
        计算日线级别全部hei_kin_ashi数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_HA()")

    def write_date_all_bias(self):
        """
        计算日线级别全部bias数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_BIAS()")

    def write_date_all_variance(self):
        """
        计算日线级别全部variance数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.WRITE_VARIANCE()")

    def write_date_unite_relative_price_index(self):
        """
        计算日线级别全部unite_relative_price_index数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_C_F_DATE_CONTRACT_DATA.CAL_UNITE_RELATIVE_PRICE_INDEX()")

    ########################################### 增量计算数据 ########################################
    def write_date_contract_basic_data_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的last_close_price、rising_and_falling_amount和price_change字段
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.WRITE_BASIC_DATA_BY_DATE", [date])

    def write_date_contract_ma_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的MA数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.WRITE_MOVING_AVERAGE_BY_DATE", [date])

    def write_date_contract_macd_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的MACD数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.WRITE_MACD_EMA_BY_DATE", [date])
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.WRITE_MACD_DIF_BY_DATE", [date])
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.WRITE_MACD_DEA_BY_DATE", [date])
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.WRITE_MACD_BY_DATE", [date])

    def write_date_contract_kd_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的KD数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.WRITE_KD_BY_DATE_RSV", [date])
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.WRITE_KD_BY_DATE_K", [date])
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.WRITE_KD_BY_DATE_D", [date])

    def write_date_contract_hei_kin_ashi_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的hei kin ashi数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.WRITE_HA_BY_DATE", [date])

    def write_date_contract_variance_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的variance数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.WRITE_VARIANCE_BY_DATE", [date])

    def write_date_contract_boll_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的BOLL数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.CAL_BOLL_BY_DATE", [date])

    def write_date_contract_bias_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的BIAS数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_C_F_DATE_CONTRACT_DATA.WRITE_BIAS_BY_DATE", [date])

    ########################################### 创建图片 ########################################
    def distinct_code_between_transaction_date_like_code(self, code, begin_date, end_date):
        """
        在一定时间范围内，所有具体合约的code
        """
        with connection.cursor() as cursor:
            cursor.execute("select distinct t.code "
                           "from c_f_date_contract_data t "
                           "where t.transaction_date between %s and %s "
                           "and t.code like %s",
                           [DatetimeUtil.str_to_datetime(begin_date, DatetimeFormat.Date_Format),
                            DatetimeUtil.str_to_datetime(end_date, DatetimeFormat.Date_Format),
                            code+"%"])
            code_tuple = cursor.fetchall()
            return code_tuple

    def find_by_code_between_transaction_date_order_by_transaction_date_asc(self, code, begin_date, end_date):
        """
        在一定时间范围内，某种具体合约的数据，并按日期升序排列
        """
        with connection.cursor() as cursor:
            cursor.execute("select * "
                           "from c_f_date_contract_data t "
                           "where t.transaction_date between %s and %s "
                           "and t.code = %s "
                           "order by t.transaction_date asc",
                           (DatetimeUtil.str_to_datetime(begin_date, DatetimeFormat.Date_Format),
                            DatetimeUtil.str_to_datetime(end_date, DatetimeFormat.Date_Format),
                            code))
            _list = cursor.fetchall()
            return _list

    ########################################### robot ########################################

    def find_distinct_transaction_date_between_transaction_date_order_by_transaction_date(self, begin_date, end_date):
        """
        查找开始时间和结束时间之间的日期，并去重
        """

        with connection.cursor() as cursor:
            cursor.execute("select distinct t.transaction_date from c_f_date_contract_data t "
                           "where t.transaction_date between %s and %s "
                           "order by t.transaction_date asc",
                           (DatetimeUtil.str_to_datetime(begin_date, DatetimeFormat.Date_Format),
                            DatetimeUtil.str_to_datetime(end_date, DatetimeFormat.Date_Format)))
            transaction_date_tuple = cursor.fetchall()
            return transaction_date_tuple

    ########################################### 查询 ########################################

    def find_by_code_and_before_n_transaction_date_order_by_transaction_date_desc(self, code: str, transaction_date: str, n_date: int) -> list[CommodityFutureDateContractData]:
        """
        根据code，查询transaction_date之前的n天记录，并按降序排列
        """

        with connection.cursor() as cursor:
            cursor.execute("select * from ("
                           "select * "
                           "from c_f_date_contract_data t "
                           "where t.code = %s and t.transaction_date <= to_date(%s,'yyyy-mm-dd') "
                           "order by t.transaction_date desc) "
                           "where rownum <= %s",
                           (code, transaction_date, n_date))
            _tuple = cursor.fetchall()
            if _tuple is not None and len(_tuple) > 0:
                commodity_future_date_contract_data_list: list[CommodityFutureDateContractData] = list()
                for t in _tuple:
                    commodity_future_date_contract_data: CommodityFutureDateContractData = self.tuple_to_commodity_future_date_contract_data(t)
                    commodity_future_date_contract_data_list.append(commodity_future_date_contract_data)
                return commodity_future_date_contract_data_list
            else:
                return None

    def find_main_contract_code(self, commodity_future_info_code: str, transaction_date: str) -> str:
        """
        查询主力合约的code
        """

        with connection.cursor() as cursor:
            sql: str = "select * from c_f_date_contract_data t where REGEXP_SUBSTR(SUBSTR(t.code, INSTR(t.code, '.', 1) + 1), '[A-Z|a-z]+', 1) = '" + commodity_future_info_code + "' and t.transaction_date = to_date('" + transaction_date + "','yyyy-mm-dd')"
            cursor.execute(sql)
            _tuple = cursor.fetchall()
            if _tuple is not None and len(_tuple) > 0:
                max_turnover: int = 0
                max_code: str = None
                for t in _tuple:
                    code: str = t[1]
                    turnover: int = t[10]
                    if turnover > max_turnover:
                        max_turnover = turnover
                        max_code = code
                return max_code
            else:
                return None

    def find_main_contract_by_transaction_date(self, transaction_date: str):
        """
        查询某一日的所有主力合约
        """

        with connection.cursor() as cursor:
            sql: str = "select REGEXP_SUBSTR(SUBSTR(t.code, INSTR(t.code, '.', 1) + 1), '[A-Z|a-z]+', 1), t.code, max(t.turnover) " \
                        "from c_f_date_contract_data t where t.transaction_date=to_date({}, 'yyyy-mm-dd') group by " \
                        "REGEXP_SUBSTR(SUBSTR(t.code, INSTR(t.code, '.', 1) + 1), '[A-Z|a-z]+', 1), t.code " \
                        "order by REGEXP_SUBSTR(SUBSTR(t.code, INSTR(t.code, '.', 1) + 1), '[A-Z|a-z]+', 1) asc, max(t.turnover) desc".format(transaction_date)
            cursor.execute(sql)
            _tuple = cursor.fetchall()
            if _tuple is not None and len(_tuple) > 0:
                main_contract_list: list[MainContract] = list()
                first_main_contract: MainContract = None
                for t in _tuple:
                    if first_main_contract is None:
                        main_contract: MainContract = MainContract(t[0], t[1], DatetimeUtil.str_to_datetime(transaction_date, DatetimeFormat.Date_Format), t[2])
                        main_contract_list.append(main_contract)
                        first_main_contract = main_contract
                    elif first_main_contract.abbreviation_code != t[0]:
                        main_contract: MainContract = MainContract(t[0], t[1], DatetimeUtil.str_to_datetime(transaction_date, DatetimeFormat.Date_Format), t[2])
                        main_contract_list.append(main_contract)
                        first_main_contract = main_contract
                return main_contract_list
            else:
                return None

    def find_last_commodity_future_date_contract_data_by_code_and_date(self, code: str, transaction_date: str) -> CommodityFutureDateContractData:
        """
        根据code和transaction_date，查询上一个交易日的记录
        """

        with connection.cursor() as cursor:
            cursor.execute("select * from (select * from c_f_date_contract_data t "
                           "where t.code = %s and t.transaction_date < to_date(%s,'yyyy-mm-dd') order by t.transaction_date desc) "
                           "where rownum<=1", [code, transaction_date])
            last_list = cursor.fetchall()
            if last_list is not None and len(last_list) > 0:
                _tuple = last_list[0]
                commodity_future_date_contract_data: CommodityFutureDateContractData = self.tuple_to_commodity_future_date_contract_data(_tuple)
                return commodity_future_date_contract_data
            else:
                return None

    def find_by_code_and_transaction_date(self, code: str, transaction_date: str) -> CommodityFutureDateContractData:
        """
        根据code和transaction_date查询记录
        """

        with connection.cursor() as cursor:
            cursor.execute("select * from c_f_date_contract_data t "
                           "where t.code = %s and t.transaction_date = to_date(%s,'yyyy-mm-dd')", [code, transaction_date])
            _tuple = cursor.fetchone()
            if _tuple is not None and len(_tuple) > 0:
                commodity_future_date_contract_data: CommodityFutureDateContractData = self.tuple_to_commodity_future_date_contract_data(_tuple)
                return commodity_future_date_contract_data
            else:
                return None

    def tuple_to_commodity_future_date_contract_data(self, t) -> CommodityFutureDateContractData:
        """
        将tuple类型转换为CommodityFutureDateContractData类型
        """

        commodity_future_date_contract_data: CommodityFutureDateContractData = CommodityFutureDateContractData()
        commodity_future_date_contract_data.id = t[0]
        commodity_future_date_contract_data.code = t[1]
        commodity_future_date_contract_data.transaction_date = t[2]
        commodity_future_date_contract_data.name = t[3]
        commodity_future_date_contract_data.highest_price = t[4]
        commodity_future_date_contract_data.lowest_price = t[5]
        commodity_future_date_contract_data.open_price = t[6]
        commodity_future_date_contract_data.close_price = t[7]
        commodity_future_date_contract_data.last_close_price = t[8]
        commodity_future_date_contract_data.volume = t[9]
        commodity_future_date_contract_data.turnover = t[10]
        commodity_future_date_contract_data.open_interest = t[11]
        commodity_future_date_contract_data.buying = t[12]
        commodity_future_date_contract_data.selling = t[13]
        commodity_future_date_contract_data.rising_and_falling_amount = t[14]
        commodity_future_date_contract_data.price_change = t[15]
        commodity_future_date_contract_data.ma5 = t[16]
        commodity_future_date_contract_data.ma10 = t[17]
        commodity_future_date_contract_data.ma20 = t[18]
        commodity_future_date_contract_data.ma60 = t[19]
        commodity_future_date_contract_data.ma120 = t[20]
        commodity_future_date_contract_data.ma250 = t[21]
        commodity_future_date_contract_data.ema12 = t[22]
        commodity_future_date_contract_data.ema26 = t[23]
        commodity_future_date_contract_data.dif = t[24]
        commodity_future_date_contract_data.dea = t[25]
        commodity_future_date_contract_data.rsv = t[26]
        commodity_future_date_contract_data.k = t[27]
        commodity_future_date_contract_data.d = t[28]
        commodity_future_date_contract_data.up = t[29]
        commodity_future_date_contract_data.mb = t[30]
        commodity_future_date_contract_data.dn = t[31]
        commodity_future_date_contract_data.ha_open_price = t[32]
        commodity_future_date_contract_data.ha_close_price = t[33]
        commodity_future_date_contract_data.ha_highest_price = t[34]
        commodity_future_date_contract_data.ha_lowest_price = t[35]
        commodity_future_date_contract_data.bias5 = t[36]
        commodity_future_date_contract_data.bias10 = t[37]
        commodity_future_date_contract_data.bias20 = t[38]
        commodity_future_date_contract_data.bias60 = t[39]
        commodity_future_date_contract_data.bias120 = t[40]
        commodity_future_date_contract_data.bias250 = t[41]
        commodity_future_date_contract_data.variance5 = t[42]
        commodity_future_date_contract_data.variance10 = t[43]
        commodity_future_date_contract_data.variance20 = t[44]
        commodity_future_date_contract_data.variance60 = t[45]
        commodity_future_date_contract_data.variance120 = t[46]
        commodity_future_date_contract_data.variance250 = t[47]
        commodity_future_date_contract_data.unite_relative_price_index = t[48]
        commodity_future_date_contract_data.create_time = t[49]
        commodity_future_date_contract_data.macd = t[50]

        return commodity_future_date_contract_data